
SELECT * FROM fn_TotalOverlapCount(1,6,100)   --1357 rows - 20 sec

	SELECT * FROM TFBindingSites tf inner join TFBindingSitesDetails tfd on 
	 tf.TFBindingSitesDetailsId=tfd.TFBindingSitesDetailsId
	  inner join UserTFSites utf on tf.Chr=utf.Chr
	 where tfd.TFBindingSitesDetailsId=1 and utf.UserDataDetailId=6 
	 and dbo.bpTotalOverlaps(tf.start, tf.[end], utf.start, utf.[end])>=100 --1357 rows 4:08 min
	 

DECLARE @bpOverlape INT
SET @bpOverlape=100
-- A is a ref TFs and B is User TF
		select B.UserDataDetailId, A.TFBindingSitesDetailsId, tfd.CellLine, tfd.TFName, 
		A.Chr Chr_A, A.start Start_A, A.[end] End_A,
	B.Chr Chr_B, B.start Start_B, B.[end] End_B,
		tfd.Active Active_A
	from  TFbindingSites A inner join UserTFSites B on A.Chr=B.Chr
	 inner join TFBindingSitesDetails tfd on A.TFBindingSitesDetailsId=tfd.TFBindingSitesDetailsId
		where tfd.Active=1 
	 and tfd.TFBindingSitesDetailsId=1 and B.UserDataDetailId=6 
	AND
	@bpOverlape <= CASE 
			WHEN B.[end] <= A.[END] AND B.Start >=A.Start
				THEN (B.[End] - B.Start) 
			WHEN B.[end] <= A.[END] AND B.Start <=A.Start
				THEN (B.[End] - A.Start) 
			WHEN B.[end] >= A.[END] AND B.Start <=A.[end]
				THEN (A.[End] - B.Start) 
			END
--Another way to avoid CASE-WHEN
	--(
	--	((B.[end] <= A.[END] AND B.Start >=A.Start) AND (B.[End] - B.Start)>= @bpOverlape)
	--	OR
	--	((B.[end] <= A.[END] AND B.Start <=A.Start) AND (B.[End] - A.Start)>=@bpOverlape)
	--	OR
	--	((B.[end] >= A.[END] AND B.Start <=A.[end]) AND (A.[End] - B.Start)>= @bpOverlape)
	--)	
		
select * from UserTFSites where start=14996418
select * from TFBindingSites A where 
100<= CASE 
			WHEN 14996904 <= A.[END] AND 14996418 >=A.Start
				THEN (14996904 - 14996418) 
			WHEN 14996904 <= A.[END] AND 14996418 <=A.Start
				THEN (14996904 - A.Start) 
			WHEN 14996904 >= A.[END] AND 14996418 <=A.[end]
				THEN (A.[End] - 14996418) 
			END
			
DECLARE @TF dbo.TFdetailsID_type
INSERT @TF(Id) VALUES(1),(2),(3)
DECLARE	@return_value int

EXEC	@return_value = [dbo].[sp_GetAllCommonOverlapedSites]
		@TFDetailsID=@TF,
		@bpOverlape = 100,
		@UserDataDetailId = 6

SELECT	'Return Value' = @return_value
			